const express = require('express');
const router = express.Router();
const conn = require('../../db/mysql');
const { jwtCheck } = require('../../utils/jwt')
const fs = require('fs');
const path = require('path');
const e = require('express');
conn.getConnection((err, conn) => {
  if (err) {
    console.log("连接失败,失败信息为：" + err);
  }
})

/**
 * 获取所有文章信息
 * @summary 获取所有文章信息
 * @route GET /article
 * @group  article
 * @returns {object} 200 - {code: 200,msg: "获取成功",type: "success",data: {}}
 * @returns {Error}  default - {code:400,msg: "获取成功",type: "fail",data:{ }}
 * @security JWT
 */
router.get('/article', (req, res, next) => {
  const sql = `SELECT * FROM  article`;
  let dataTemplent = {
    code: 200,
    type: "success",
    msg: "查询成功",
    data: {}
  }
  conn.query(sql, (err, result) => {
    if (err) {
      dataTemplent.code = 400;
      dataTemplent.type = "fail";
      dataTemplent.msg = "查询失败";
    } else {
      dataTemplent.data = result;
    }
    res.send(dataTemplent);
  });
});
/**
 * 获取所有文章信息（分页）
 * @summary 获取所有文章信息（分页）
 * @route GET /articles
 * @group  article
 * @param {string} articleName.query - 文章名
 * @param {number} status.query - 状态
 * @param {number} sortId.query - 分类号
 * @param {number} pageSize.query.required - 分页大小
 * @param {number} page.query.required - 页码
 * @returns {object} 200 - {code: 200,msg: "获取成功",type: "success",data: {}}
 * @returns {Error}  default - {code:400,msg: "获取成功",type: "fail",data:{ }}
 * @security JWT
 */
router.get('/articles', (req, res, next) => {
  // console.log(jwtCheck(req.headers.authorization))
  let status = req.query.status;
  let sortId = req.query.sortId;
  let articleName = req.query.articleName;
  let orderType = req.query.orderType;
  let orderValue = req.query.orderValue;
  let orderSql = '';
  let statusSql = '';
  let statusSql2 = '';
  let sortIdSql = '';
  let sortIdSql2 = '';
  let articleNameSql = '';
  let articleNameSql2 = '';
  if (articleName) {
    articleNameSql = `and e.title LIKE '%${articleName}%'`;
    articleNameSql2 = `and title LIKE '%${articleName}%'`;
  }
  if (sortId) {
    sortIdSql = `and e.sort_id='${sortId}'`;
    sortIdSql2 = `and sort_id='${sortId}'`;
  }
  if (status) {
    statusSql = `and e.status='${status}'`
    statusSql2 = `and status='${status}'`
  }
  if (orderType === '1') {// 1为创建时间
    if (orderValue === '1') {//0为升序 1为降序
      orderSql = `e.create_time desc`;
    } else {
      orderSql = `e.create_time asc`;
    }
  } else { // 0为更新时间
    if (orderValue === '1') {//0为升序 1为降序
      orderSql = `e.update_time desc`;
    } else {
      orderSql = `e.update_time asc`;
    }
  }
  let pageSize = req.query.pageSize;
  let pageCurrent = req.query.page;
  let start = (pageCurrent - 1) * pageSize;
  let sql = `select e.article_id,d.type_name,e.title,e.create_time,e.update_time,e.status 
          from article as e 
          join articletype as d 
          on e.sort_id = d.type_id 
          where 1=1 ${sortIdSql} ${statusSql} ${articleNameSql}
          order by ${orderSql}
          LIMIT ${start},${pageSize};`
  // 先where，再order by，最后limit
  let dataTemplent = {
    code: 200,
    msg: "查询成功",
    type: "success",
    data: {}
  }
  conn.query(sql, (err, result) => {
    if (err) throw err;
    let list = result
    conn.query(`SELECT COUNT(*) AS total FROM  article WHERE 1=1 ${articleNameSql2} ${sortIdSql2} ${statusSql2}`, (err, result1) => {
      if (err) {
        console.log(err)
        dataTemplent.code = 400;
        dataTemplent.type = "fail";
        dataTemplent.msg = "查询失败";
      }
      else {
        let obj = {
          pageSize: pageSize,
          pageCurrent: pageCurrent,
          total: result1[0].total,
          list: list
        }
        dataTemplent.data = obj;
      }
      res.send(dataTemplent);
    })
  })
});
/**
 * 根据ID获取文章信息
 * @summary 根据ID获取文章信息
 * @route GET /articleById
 * @group  article
 * @param {number} page.query.id
 * @returns {object} 200 - {code: 200,msg: "获取成功",type: "success",data: {}}
 * @returns {Error}  default - {code:400,msg: "获取成功",type: "fail",data:{ }}
 * @security JWT
 */
router.get('/articleById', (req, res, next) => {
  const sql = `SELECT * FROM  article where article_id='${req.query.id}'`;
  let dataTemplent = {
    code: 200,
    type: "success",
    msg: "查询成功",
    data: {}
  }
  conn.query(sql, (err, result) => {
    if (err) {
      dataTemplent.code = 400;
      dataTemplent.type = "fail";
      dataTemplent.msg = "查询失败";
    } else {
      const filePath = path.join(__dirname, '../../public/upload/cover', result[0].cover_img);
      const imageData = fs.readFileSync(filePath);
      const imageBase64 = imageData.toString("base64");
      const imagePrefix = "data:image/png;base64,";
      dataTemplent.data = result;
      dataTemplent.data[0].cover_img = imagePrefix + imageBase64;
    }
    res.send(dataTemplent);
  });
});
/**
 * 新增文章
 * @summary 新增文章
 * @route POST /article
 * @group  article
 * @param {string}  articleInfo.body.required -  articleName、sortId、content、status、coverImg、create_time、update_time
 * @returns {object} 200 - {code: 200,msg: "新增成功",type: "success",data: {}}
 * @returns {Error}  default - {code:400,msg: "新增失败",type: "fail",data:{ }}
 * @security JWT
 */
router.post('/article', (req, res, next) => {
  const params = req.body;
  const token = jwtCheck(req.headers.authorization) || {};
  const id = token.msg.account;
  let dataTemplent = {
    code: 200,
    msg: "新增成功",
    type: "success",
    data: {}
  }
  let coverUrl;
  if (params) {
    if (params.cover_img) {
      const date = new Date();
      coverUrl = id + date.getFullYear().toString() + (date.getMonth() + 1).toString() + date.getDate().toString() + date.getHours().toString() + date.getMinutes().toString() + ".png";
      const filePath = path.join(__dirname, '../../public/upload/cover', coverUrl);
      const base64 = params.cover_img.replace(/^data:image\/\w+;base64,/, "");
      const dataBuffer = Buffer.from(base64, 'base64');
      fs.writeFile(filePath, dataBuffer, function (err) { })
    }
    const sql = `INSERT INTO article VALUES('0','${params.sortId}','${params.title}','${params.content}','${coverUrl}','${params.create_time}','${params.update_time}','${params.status}');`;
    conn.query(sql, (err, result) => {
      if (err) {
        dataTemplent.code = 400;
        dataTemplent.type = "fail";
        dataTemplent.msg = "新增失败";
        console.log(err);
      } else {
        dataTemplent.data = { id: result.insertId };
      }
      res.send(dataTemplent);
    });
  }
});
/**
 * 修改文章
 * @summary 修改文章
 * @route PUT /article
 * @group  article
 * @param {string}  articleInfo.body.required -id  articleName、sortId、content、status、coverImg、status、update_time
 * @returns {object} 200 - {code: 200,msg: "修改成功",type: "success",data: {}}
 * @returns {Error}  default - {code:400,msg: "修改失败",type: "fail",data:{ }}
 * @security JWT
 */
router.put('/article', (req, res, next) => {
  let params = req.body;
  const token = jwtCheck(req.headers.authorization) || {};
  const id = token.msg.account;
  let dataTemplent = {
    code: 200,
    type: "success",
    msg: "修改成功",
    data: {}
  }
  let coverUrl;
  if (params) {
    const date = new Date();
    coverUrl = id + date.getFullYear().toString() + (date.getMonth() + 1).toString() + date.getDate().toString() + date.getHours().toString() + date.getMinutes().toString() + ".png";
    const filePath = path.join(__dirname, '../../public/upload/cover', coverUrl);
    const base64 = params.cover_img.replace(/^data:image\/\w+;base64,/, "");
    const dataBuffer = Buffer.from(base64, 'base64');
    fs.writeFile(filePath, dataBuffer, function (err) { })
    const sql = `UPDATE article set sort_id=${params.sortId},title='${params.title}',content='${params.content}',cover_img='${coverUrl}',update_time='${params.update_time}',status='${params.status}' WHERE article_id=${params.id};`;
    conn.query(sql, (err, result) => {
      if (err) {
        dataTemplent.code = 400;
        dataTemplent.type = "fail";
        dataTemplent.msg = "修改失败";
        dataTemplent.msg = err;
      } else {
      }
      res.send(dataTemplent);
    });
  }
});
/**
 * 删除文章
 * @summary 删除文章
 * @route DELETE /article
 * @group  article
 * @param {string} id.body.required -文章id
 * @returns {object} 200 - {code: 200,msg: "删除成功",type: "success",data: {}}
 * @returns {Error}  default - {code:500,msg: "删除失败",type: "fail",data:{ }}
 * @security JWT
 */
router.delete('/article', (req, res, next) => {
  let params = req.body;
  let dataTemplent = {
    code: 200,
    type: "success",
    msg: "删除成功",
    data: {}
  }
  if (params) {
    conn.query(`select status from article where article_id=${params.id} `, (err1, result1) => {
      let sql = '';
      if (result1[0].status === 0) {
        sql = `UPDATE article set status='1' WHERE article_id='${params.id}';`;
      } else {
        sql = `delete from article where article_id='${params.id}';`;
      }
      conn.query(sql, (err, result) => {
        if (err) {
          dataTemplent.code = 400;
          dataTemplent.type = "fail";
          dataTemplent.msg = "删除失败";
        } else {
          dataTemplent.data = {};
        }
        res.send(dataTemplent);
      });
    });
  }
});

/**
 * 查询每日新增文章数量
 * @summary 查询每日新增文章数量
 * @route GET /articleCount
 * @return {Object} 200 - {code: 200,msg: "查询成功",type: "success",data: {}}
 * @security JWT
 */
router.get('/articleCount', (req, res, next) => {
  let dataTemplent = {
    code: 200,
    type: "success",
    msg: "查询成功",
    data: []
  }
  let sql = `SELECT DATE(create_time) AS date, COUNT(*) AS count
          FROM article
          WHERE create_time >= DATE_SUB(CURDATE(), INTERVAL 1 MONTH)
          GROUP BY DATE(create_time)
          ORDER BY DATE(create_time) ASC`;
  conn.query(sql, (err, result) => {
    if (result) {
      dataTemplent.data = result;
      res.send(dataTemplent);
    }
  })
});
/**
 * 查询分类文章数量
 * @summary 查询分类文章数量
 * @route GET /typeCount
 * @return {Object} 200 - {code: 200,msg: "查询成功",type: "success",data: {}}
 * @security JWT
 */
router.get('/typeCount', (req, res, next) => {
  let dataTemplent = {
    code: 200,
    type: "success",
    msg: "查询成功",
    data: []
  }
  let sql = `SELECT at.type_name AS name, COUNT(*) AS value
          FROM article as a
          join articletype as at
          on a.sort_id=at.type_id
          WHERE at.is_delete=0
          GROUP BY at.type_name
          ORDER BY value ASC`;
  conn.query(sql, (err, result) => {
    if (result) {
      dataTemplent.data = result;
      res.send(dataTemplent);
    }
  })
});
/**
 * 查询本月新增文章数量
 * @summary 查询本月新增文章数量
 * @route GET /articleMouthCount
 * @return {Object} 200 - {code: 200,msg: "查询成功",type: "success",data: {}}
 * @security JWT
 */
router.get('/articleMouthCount', (req, res, next) => {
  let dataTemplent = {
    code: 200,
    type: "success",
    msg: "查询成功",
    data: []
  }
  let sql = `SELECT COUNT(*) AS count
          FROM article
          WHERE DATE_FORMAT(create_time,'%Y%m') = DATE_FORMAT(CURDATE(),'%Y%m');`;
  conn.query(sql, (err, result) => {
    if (result) {
      dataTemplent.data = result;
      res.send(dataTemplent);
    }
  })
});
module.exports = router;